package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.servlet.Constants;


/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: RegionDB.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class RegionDB extends DBPersistenceManager {
	private RegionDB(PersistenceToken token) {
		super(token);
	}

	public static RegionDB getManager(PersistenceToken token) {
		return new RegionDB(token);
	}

	public Region[] getAll() throws SQLException {
		Connection con = getConnection();
		Region[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Region item = new Region();

				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));

				items.add(item);
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Region[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public Region[] getAllByCountry(String country_iso3)
		 throws SQLException {
		Connection con = getConnection();
		Region[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_BY_COUNTRY);
			ps.setString(1, country_iso3);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Region item = new Region();

				item.setId(rs.getInt(1));
				item.setName(rs.getString(2));

				items.add(item);
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Region[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public int getLastId()
		 throws SQLException {
		Connection con = getConnection();
		int result = 0;

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}

		return result;
	}

	public void deleteRegion(int id) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_REGION);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public void insertRegion(Region region) throws SQLException {
		if (region.getId() == -1) {
			region.setId((int) db_token.getIdgenerators().nextVal(Constants.IDGEN_REGIONS));
		}

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_REGION);
			ps.setInt(1, region.getId());
			ps.setString(2, region.getCountryIso3());
			ps.setString(3, region.getName());
			ps.setDouble(4, region.getGeo_data().getLatitude());
			ps.setDouble(5, region.getGeo_data().getLongitude());
			ps.setString(6, region.getGeo_data().getGeo_code());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public Region getById(int id)
		 throws SQLException {
		Connection con = getConnection();
		Region result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setInt(1, id);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				result = new Region();
				result.setId(id);
				result.setName(rs.getString(1));
				result.setCountryIso3(rs.getString(2));
				result.getGeo_data().setLatitude(rs.getDouble(3));
				result.getGeo_data().setLongitude(rs.getDouble(4));
				result.getGeo_data().setGeo_code(rs.getString(5));
			}

			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public AIDALocation[] findRegions(String what,
							    int max_result_len)
		 throws SQLException {
		if (what == null)
			return null;

		Connection con = getConnection();
		AIDALocation[] result = null;
		ArrayList items = new ArrayList();

		try {
			PreparedStatement ps = con.prepareStatement(FIND_REGIONS);
			ps.setString(1, what + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();
				Country country = new Country();
				Region region = new Region();

				region.setId(rs.getInt(1));
				region.setCountryIso3(rs.getString(2));
				region.setName(rs.getString(3));
				country.setIso3(region.getCountryIso3());
				country.setName(rs.getString(4));

				item.setRegion(region);
				item.setCountry(country);

				items.add(item);

				if (items.size() > max_result_len)
					break;
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public void updateRegion(Region region) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_REGION);
			ps.setString(1, region.getName());
			ps.setDouble(2, region.getGeo_data().getLatitude());
			ps.setDouble(3, region.getGeo_data().getLongitude());
			ps.setString(4, region.getGeo_data().getGeo_code());
			ps.setInt(5, region.getId());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	private static final String FIND_REGIONS =
		 "SELECT r.id, r.country_iso3, r.name, c.name "
		 + "FROM regions r, countries_iso3 c "
		 + "WHERE (r.country_iso3=c.iso3) AND (r.name ILIKE ?)";
	private static final String INSERT_REGION =
		 "INSERT INTO regions (id,country_iso3,name,gs_lat,gs_long,geo_code) " + "VALUES (?,?,?,?,?,?)";
	private static final String DELETE_REGION =
		 "DELETE FROM regions " + "WHERE id=?";
	private static final String GET_LAST_ID =
		 "SELECT MAX(id) " + "from regions";
	private static final String GET_ALL =
		 "SELECT id, name,country_iso3 " + "FROM regions " + "ORDER BY name ASC";
	private static final String GET_ALL_BY_COUNTRY =
		 "SELECT id, name "
		 + "FROM regions "
		 + "WHERE country_iso3=? "
		 + "ORDER BY name ASC";
	public final String GET_BY_ID =
		 "SELECT name, country_iso3,gs_lat,gs_long,geo_code " +
		 "FROM regions " +
		 "WHERE id = ?";
	public final String UPDATE_REGION =
		 "UPDATE regions SET name=?, gs_lat=?, gs_long=?, geo_code=? " +
		 "WHERE id=?";

}
